﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
using INTEX.DataBase.Tools;
namespace WPFExcelReport
{

    public class JLTFWB18Data : BaseFunction
    {
        
        public JLTFWB18Data()
            : base()
        {

        }
        public JLTFWB18Data(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetWB18(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "WB18";
            string ART = dwABPOS["ART"].ToString().Trim();
            string STAFF = dwABPOS["STAFF"].ToString().Trim();
            string FROMDNDATE = dwABPOS["FROMDNDATE"].ToString().Trim();
            string ENDDNDATE = dwABPOS["ENDDNDATE"].ToString().Trim();
            string LANG = dwABPOS["LANG"].ToString().Trim();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT ART,COLOR,WBBPACKNR,round(AVGWIDTH,1) as AVGWIDTH,WBFKNEUDAT,WBFKWARSCH,WBFKWMPOS, "
                    + " MTR,YARD,TOTRATE,DECODE(AVGWIDTH,0,0,DECODE(YARD,0,0,ROUND(TOTRATE*36/AVGWIDTH/YARD*100,1))) AS YRATE, "
                    + " BEM,WBFFSCHL,DES,RATE,BOW,SKEW,WIDTH,TYPE,FDEFECT,EDEFECT,WBBSTKNR,WBBLB,JUDGE "
                    + " FROM (SELECT TRIM(D.WBBART) AS ART,TRIM(D.WBBFARBE) AS COLOR, "
                    + " to_number(D.WBBPACKNR) as wbbpacknr, "
                    + " (SELECT AVG(WBFBRTVON * 0.001)  FROM WBFEHL X  WHERE A.WBFKMANDANT = X.WBFMANDANT "
                    + " AND A.WBFKRWKRS = X.WBFRWKRS AND A.WBFKRWLFD = X.WBFRWLFD AND A.WBFKRWPOS = X.WBFRWPOS "
                    + " AND A.WBFKVERSION = X.WBFVERSION AND X.WBFBRTVON > 40000 AND X.WBFFSCHL = '99 ' ) AS AVGWIDTH, "
                    + " DECODE(B.WBFFSCHL,'99 ',B.WBFBRTVON * 0.001) AS WIDTH, "
                    + " DECODE(B.WBFFSCHL,'95 ',B.WBFBRTVON * 0.001) AS BOW,    "
                    + " DECODE(B.WBFFSCHL,'94 ',B.WBFBRTVON * 0.001) AS SKEW, "
                    + " A.WBFKNEUDAT,A.WBFKWARSCH,A.WBFKWMPOS, "
                    + " ROUND(d.wbbistklg*0.001,1) AS MTR, "
                    + " ROUND(d.wbbistklg/0.9144*0.001,1) AS YARD, "
                    + " (SELECT SUM(DECODE(SUBSTR(X.WBFFSCHL,3,1),'A',1,'B',2,'C',3,'D',4)) "
                    + " FROM WBFEHL X WHERE A.WBFKMANDANT = X.WBFMANDANT  "
                    + " AND A.WBFKRWKRS = X.WBFRWKRS AND A.WBFKRWLFD = X.WBFRWLFD AND D.WBBISTKLG>= X.WBFLGVON "
                    + " AND A.WBFKRWPOS = X.WBFRWPOS AND A.WBFKVERSION = X.WBFVERSION) AS TOTRATE, "
                    + " D.WBBBEM AS BEM,B.WBFFSCHL, "
                    + " (case when  '" + LANG + "'='V' THEN TRIM(SUBSTR(c.SDDATEN, 2, 20)) "
                    + " WHEN '" + LANG + "'='E' THEN trim(substr(c.sddaten,70,40)) END) AS DES, "
                    + " DECODE(SUBSTR(B.WBFFSCHL,3,1),'A',1,'B',2,'C',3,'D',4) AS RATE, "
                    + " SUBSTR(C.SDDATEN, 44, 1) AS TYPE, "
                    + " B.WBFLGVON * 0.001 AS FDEFECT, "
                    + " B.WBFLGBIS * 0.001 AS EDEFECT, "
                    + " D.WBBSTKNR,WBBMAART,WBBAUSFGRI,D.WBBLB,'' AS JUDGE "
                    + " FROM WBFKOPF A,WBFEHL B,SDDAT C,WBBST D "
                    + " WHERE A.WBFKMANDANT = B.WBFMANDANT AND A.WBFKRWKRS = B.WBFRWKRS "
                    + " AND A.WBFKRWLFD = B.WBFRWLFD AND A.WBFKRWPOS = B.WBFRWPOS "
                    + " AND A.WBFKVERSION = B.WBFVERSION AND B.WBFMANDANT||B.WBFFSCHL = SUBSTR(C.SDKEY,1,5) "
                    + " AND A.WBFKMANDANT = D.WBBMANDANT AND A.WBFKRWKRS = D.WBBRWKRS "
                    + " AND A.WBFKRWLFD = D.WBBRWLFD AND A.WBFKRWPOS = D.WBBPOS AND D.WBBISTKLG>=B.WBFLGVON "
                    + " AND C.SDSA = '0267' AND (B.WBFFSCHL < '900' or B.WBFFSCHL in ('94 ','95 ','99 ') ) and B.WBFVERSION ='1' "
                    + " AND D.WBBART LIKE '%" + ART + "%' "
                    + " AND (TRIM(A.WBFKWARSCH) = '" + STAFF + "' OR '" + STAFF + "' = 'AAAAA' ) "
                    + " AND (A.WBFKNEUDAT >= " + FROMDNDATE + " AND A.WBFKNEUDAT <= " + ENDDNDATE + ") "
                    + " AND A.WBFKURWAKZ = 'F')  "
                    + " ORDER BY 1,2,3,WBBSTKNR,FDEFECT "
                    ;
                /* + " NVL((SELECT TRIM(K.TXTKTEXT) FROM TXTIND T,TXTKON K WHERE T.TXTIMANDANT = K.TXTKMANDANT "
                    + " AND T.TXTIPIND = K.TXTKPIND AND T.TXTITXTART= 'WBS' AND K.TXTKPOS = '10'  "
                    + " AND T.TXTISIND LIKE A.WBFKRWKRS||A.WBFKRWLFD||A.WBFKRWPOS||'%' ),D.WBBBEM)*/
                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value,type;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["BEM"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["BEM"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["DES"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["DES"] = value;
                        type = dw["TYPE"].ToString().Trim();
                        if (LANG == "V")
                        {
                            dw["JUDGE"] = "通过";
                            if (type == "F")
                            {
                                dw["TYPE"] = "整理";
                            }
                            else if (type == "G")
                            {
                                dw["TYPE"] = "织造";
                            }
                            else if (type == "W")
                            {
                                dw["TYPE"] = "浆纱";
                            }
                            else if (type == "R")
                            {
                                dw["TYPE"] = "染色";
                            }
                            else
                            {
                                dw["TYPE"] = "其它";
                            }
                        }
                        if (LANG == "E")
                        {
                            dw["JUDGE"] = "Pass";
                            if (type == "F")
                            {
                                dw["TYPE"] = "Finishing";
                            }
                            else if (type == "G")
                            {
                                dw["TYPE"] = "Weaving";
                            }
                            else if (type == "W")
                            {
                                dw["TYPE"] = "Sizing";
                            }
                            else if (type == "R")
                            {
                                dw["TYPE"] = "Dyeing";
                            }
                            else
                            {
                                dw["TYPE"] = "Other";
                            }
                        }
                        
                    }
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }       
        public DataSet BuildJLTFWB18Data(DataRow dwUI)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                setResult = GetWB18(dwUI);
                setReturn.Merge(setResult);                
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
    }
}